Namespace persistent

    Public Class Bulk_Service_Requests

        Public Shared Function GetGridTable(ByVal p_custcode As String, ByVal p_SmeLAFlag As String) As DataTable
            Dim sql As String = "SELECT PROCESS, DES, ASSIGN_ALLOWED, REMOVE_ALLOWED, substr(SNCODE, -3) SNCODE " & _
                                "FROM " & _
                                "( " & _
                                "	SELECT  'x' PROCESS, b.DES, a.ASSIGN_ALLOWED, a.REMOVE_ALLOWED, " & _
                                "		   CASE WHEN a.SNCODE = '062' THEN '999' ELSE '' END || substr('000' || to_char(a.SNCODE), -3) SNCODE " & _
                                "	FROM BULK_SERVICES a, MPUSNTAB b " & _
                                "	WHERE a.SNCODE = b.SNCODE [CRITERIAS]" & _
                                "	ORDER BY TO_NUMBER(SNCODE) " & _
                                ")"

            If (p_SmeLAFlag = "S" And p_custcode.Trim.StartsWith("1.")) Then
                sql = sql.Replace("[CRITERIAS]", "and a.SNCODE not in (61)")
            Else
                sql = sql.Replace("[CRITERIAS]", "and a.SNCODE not in (54)")
            End If
            Return myDBConn.myORATable(sql)
        End Function

        Public Shared Function InsertToRequestTable(ByVal p_coid As String, ByVal p_sncode As String, ByVal p_action As String, ByVal p_user As String) As String
            Dim sql As String
            p_sncode = CStr(CInt(p_sncode))
            'sql = "insert into BULK_SERVICE_REQUESTS (ID, CO_ID, SNCODE, ACTION, PROCESS_DATE, PROCESS_STATUS, RESULT, MODIFIED_BY) values (BSR_SEQ.nextval, [TAG1], [TAG2], '[TAG3]', sysdate, 'O', null, '[TAG4]')"
            sql = "insert into eu_bill.service_assigner_orders(ID, CO_ID, SNCODE, ACTION, STATUS, REQUEST_DATE, REQUEST_SOURCE, PROCESS_DATE) values(eu_bill.service_assigner_orders_seq.nextval, [TAG1], [TAG2], '[TAG3]', 'O', sysdate, 'TOPLU KURUMSAL - [TAG4]', null) "

            sql = sql.Replace("[TAG1]", p_coid)
            sql = sql.Replace("[TAG2]", p_sncode)
            sql = sql.Replace("[TAG3]", p_action)
            sql = sql.Replace("[TAG4]", p_user)

            myDBConn.ORAExecute(sql)
        End Function

        Public Shared Function GetCompanyContracts(ByVal p_custcode As String, ByVal p_companylevel As String) As DataTable
            Dim l_dt As DataTable
            Dim l_SME_LA As String
            Dim sql As String
            Dim l_HighestCustcode As String
            Dim l_HighestCustomerid As String

            sql = "select toplevel.CUSTCODE, toplevel.CUSTOMER_ID, decode(decode(nvl(curlevel.CSLEVEL,0), [TAG2], curlevel.CUSTOMER_ID, nvl(curlevel.CUSTOMER_ID_HIGH, 0)), 0, 'S', 'L') SME_LA_FLAG " + vbCrLf
            sql = sql + "	from CUSTOMER_ALL curlevel, CUSTOMER_ALL toplevel  " + vbCrLf
            sql = sql + "	where curlevel.CUSTCODE = '[TAG1]' " + vbCrLf
            sql = sql + "		and decode(decode(nvl(curlevel.CSLEVEL,0), [TAG2], curlevel.CUSTOMER_ID, nvl(curlevel.CUSTOMER_ID_HIGH, 0)), " + vbCrLf
            sql = sql + "				0, (select CUSTOMER_ID from CONTRACT_ALL where CO_ID = (select max(a.CO_ID) from CONTRACT_ALL a, CURR_CO_STATUS b where a.CUSTOMER_ID = curlevel.CUSTOMER_ID and a.CO_ID = b.CO_ID and b.CH_STATUS <> 'd')), " + vbCrLf
            sql = sql + "				sysadm.fn_la_getcustidhigh([TAG2], curlevel.CUSTOMER_ID)) = toplevel.CUSTOMER_ID " + vbCrLf
            sql = sql.Replace("[TAG1]", p_custcode)
            sql = sql.Replace("[TAG2]", p_companylevel)
            Dim l_dt2 As DataTable
            l_dt2 = myDBConn.myORATable(sql)
            l_HighestCustcode = CStr(l_dt2.Rows(0).Item("CUSTCODE"))
            l_SME_LA = CStr(l_dt2.Rows(0).Item("SME_LA_FLAG"))
            l_HighestCustomerid = CStr(l_dt2.Rows(0).Item("CUSTOMER_ID"))

            If l_SME_LA = "L" Then
                sql = "select  'Y' CHOOSE, b.CO_ID " + vbCrLf
                sql = sql + "   from CUSTOMER_ALL a, CONTRACT_ALL b, CURR_CO_STATUS c " + vbCrLf
                sql = sql + "   where (a.CUSTCODE = '[TAG1]' or a.CUSTCODE like '[TAG1].%') " + vbCrLf
                sql = sql + "       and a.CUSTOMER_ID = b.CUSTOMER_ID " + vbCrLf
                sql = sql + "		and b.CO_ID = c.CO_ID " + vbCrLf
                sql = sql + "		and c.CH_STATUS = 'a' " + vbCrLf

                sql = sql.Replace("[TAG1]", l_HighestCustcode)
                l_dt = myDBConn.myORATable(sql)
            ElseIf l_SME_LA = "S" Then
                sql = "select  'Y' CHOOSE, a.CO_ID " + vbCrLf
                sql = sql + "	from CONTRACT_ALL a, CURR_CO_STATUS b " + vbCrLf
                sql = sql + "	where a.CUSTOMER_ID = [TAG1] " + vbCrLf
                sql = sql + "		and a.CO_ID = b.CO_ID " + vbCrLf
                sql = sql + "		and b.CH_STATUS = 'a' " + vbCrLf
                sql = sql.Replace("[TAG1]", l_HighestCustomerid)
                l_dt = myDBConn.myORATable(sql)
            End If
            Return l_dt
        End Function

    End Class

End Namespace
